﻿CREATE PROCEDURE [RestartFramework].[RemoveETLJob]
		@pETLJobName				SYSNAME
,		@pRemovedJobStagesTally		SMALLINT	=	NULL	OUTPUT
,		@pRemovedJobHistoryTally	SMALLINT	=	NULL	OUTPUT
,       @pPrint                     BIT         =   1
AS
BEGIN
		SET NOCOUNT ON;
		DECLARE  @vRowCount  INT = 0;
		SET	@pRemovedJobStagesTally		=	0	;
		SET	@pRemovedJobHistoryTally	=	0	;
		BEGIN TRAN;
		BEGIN TRY
				DELETE
				FROM	[RestartFramework].[ETLJobStage]
				WHERE	[ETLJobId]	IN	(
						SELECT	[ETLJobId]
						FROM	[RestartFramework].[ETLJob]
						WHERE	[ETLJobName] = @pETLJobName
						);
				SET	@pRemovedJobStagesTally = @@ROWCOUNT;
				IF(@pPrint = 1)
						PRINT   CONVERT(NVARCHAR(5),@pRemovedJobStagesTally) + N' rows deleted from [RestartFramework].[ETLJobStage] for ETLJobName=' + @pETLJobName;
				DELETE
				FROM	[RestartFramework].[ETLJobHistory]
				WHERE	[ETLJobId]	IN	(
						SELECT	[ETLJobId]
						FROM	[RestartFramework].[ETLJob]
						WHERE	[ETLJobName] = @pETLJobName
						);
				SET	@pRemovedJobHistoryTally = @@ROWCOUNT;
				IF(@pPrint = 1)
						PRINT   CONVERT(NVARCHAR(5),@pRemovedJobHistoryTally) + N' rows deleted from [RestartFramework].[ETLJobHistory] for ETLJobName=' + @pETLJobName;
				DELETE
				FROM	[RestartFramework].[ETLJob]
				WHERE	[ETLJobName]	=	@pETLJobName;
				SET	@vRowCount = @@ROWCOUNT;
				IF(@pPrint = 1)
						PRINT   CONVERT(NVARCHAR(5),@vRowCount) + N' rows deleted from [RestartFramework].[ETLJob] for ETLJobName=' + @pETLJobName;
		END TRY
		BEGIN CATCH
				IF @@TRANCOUNT > 0
						ROLLBACK TRAN;
				THROW;
		END CATCH
		IF @@TRANCOUNT > 0
				COMMIT TRAN;
END
